package cn.briarbear.myblog.repository;

import cn.briarbear.myblog.entity.table.TagBlog;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * 标签博客对应表操作持久化接口
 */
@CacheConfig(cacheNames = "tagBlog")
public interface TagBlogRepository extends JpaRepository<TagBlog,Integer> {

//    @Query(value = "select t.id,t.name,count(tb.id) as blogCount from Tag t inner join TagBlog tb on t.id = tb.tagId group by t.id")
//    @Query(value = "select tb from TagBlog tb where tb.tagId = 1")
//    @Query(nativeQuery = true,value = "select DISTINCT t.id,t.tag_name,count(tb.id) as BlogCount from tag t left join tag_blog tb on t.id = tb.tag_id group by t.id")
//    @Query(value = "select new TagView(t.id,t.tag_name,count(tb.id)) from Tag t inner join TagBlog tb on t.id = tb.tag_id order by t.id")
//    @Query(nativeQuery = true,value = "select t.id,t.name,count(l.id) as n from tag t,link l where t.id = l.tag group by t.id")
//    @Query(nativeQuery = true,value = "select t.id,t.name,count(tb.id) as num from tag t inner join tag_blog tb on t.id = tb.tag_id group by t.id ") //原生SQL方法
//    @Query(value = "select t.id,t.name,count(l.id) as n from Tag t,TagBlog l where t.id = l.tagId group by t.id") //JPQL方法
    @Cacheable
    @Query(value = "select t.id,t.name,count(l.id) as n from Tag t,TagBlog l where t.id = l.tagId group by t.id") //JPQL方法
    List<Object> selectAllTagView();

    @Transactional
    @Modifying
    @Query(value = "delete from TagBlog tb where tb.blogId=?1")
    void deleteByBlogId(Integer blogId);
}